<?php
/* 
 *	Author: Shaun Farrell [www.parkitdc.com]
 * 	Description: Script takes KML parking meter data and parses it into the database.  Loads 
 * 	all parking meter data that the city has.  To install just fill in variables at top and run script.
 * 	
 *	THIS SCRIPT IS ONLY USED TO LOAD PARKING METER DATA INTO THE DATABASE.
 *
 */
 
 	define("DATABASE_SERVER", "localhost");
  	define("DATABASE_USERNAME", "root");
   	define("DATABASE_PASSWORD","");
    define("DATABASE_SCHEMA", "parkitdc");
    define("KML_FILE","doc.kml");
      
/*
 *	DON'T EDIT BELOW THIS LINE
 */
setlocale(LC_MONETARY, 'en_US');

$link = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
// make foo the current db
$db_selected = mysql_select_db(DATABASE_SCHEMA, $link);
if (!$db_selected) {
    die ('Can\'t use apps08 : ' . mysql_error());
}

if (file_exists(KML_FILE)) {
	$xml = simplexml_load_file(KML_FILE);
    
   	foreach($xml->Document->Placemark as $meter) {
   		$sql = "";
   		//echo $meter->name."<br />";
   		$latitude = substr($meter->Point->coordinates,0, strpos($meter->Point->coordinates, ","));
   		//echo $latitude."<br />";
   		$longitude = substr($meter->Point->coordinates, (strlen($latitude)+1),-2);
   		//echo $longitude."<br />";
   		$pieces = explode("<br>", $meter->description);
   		$sql .= "INSERT INTO Parking VALUES (";
   		foreach($pieces as $p) {
   			$key = substr($p, 0, strpos($p,"="));
   			$value = substr($p,strpos($p,"=") + 1);
   			switch($key) {
   				case "METERNUM":
   					//echo "METERNUM-".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "BLOCK":
   					//echo "BLOCK-".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "STREET":
   					//echo "STREET-".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "SIDE":
   					//echo "SIDE-".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "HOUR_FROM":
   					$from_len = strlen($value);
   					if($from_len === 3) {
   						$HOUR = substr($value,0,1);
   						$MIN = substr($value,1,2);
   					} else {
   						$HOUR = substr($value,0,2);
   						$MIN = substr($value,2,2);
   					}
   					$sql .= "'".mysql_real_escape_string($HOUR.":".$MIN." AM")."', ";
   					break;
   				case "HOUR_TO":
   					$to_len = strlen($value);
   					if($to_len === 3) {
   						$HOUR = substr($value,0,1);
   						$MIN = substr($value,1,2);
   					} else {
   						$HOUR = substr($value,0,2);
   						$MIN = substr($value,2,2);
   					}
 
   					$sql .= "'".mysql_real_escape_string($HOUR.":".$MIN." PM")."', ";
   					break;
   				case "DAY_FROM":
   					//echo "DF-".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "DAY_TO":
   					//echo "DT-".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "LIMIT":
   					//echo "LIMIT-".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "RATE":
					$RATE = money_format('%.2n', $value);
					$sql .= "'".mysql_real_escape_string($RATE)."', ";
   					break;
   				case "MAINT_ROUT":
   					//echo "MR".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "COLLECTION":
   					//echo "Collection".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "WARD":
   					//echo "WARD-".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "STAT":
   					//echo "STAT-". $value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "COMMENTS":
   					//echo "COMMENTS-".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   				case "YEAR":
   					//echo "YEAR-".$value." ";
   					$sql .= "'".mysql_real_escape_string($value)."', ";
   					break;
   			}
   		}
   		$sql .= "'".$latitude."', '".$longitude."');";
   		
   		//echo $sql."<br /><br />";
   		mysql_query($sql);
    	
    }
} else {
    exit('Failed Check Script.');
}
?>
